<?php
function printYear($date)
{
	return "<td class=\"EventReportYear\" colspan=\"4\">".$date->format("Y")."</td></tr><tr>";
}

function printMonth($date)
{
	return "<td class=\"EventReportMonth\" colspan=\"4\">".$date->format("F")."</td></tr><tr>";
}

function printDay($date)
{
	return "<td class=\"EventReportDay\" colspan=\"4\">".$date->format("j l")."</td></tr><tr>";
}

function compare1($a,$b)
{
//	print_r ($a)."\n";
	if (new DateTime($a['Start']) == new DateTime($b['Start'])) 
	{
		return 0;
	}
	return (new DateTime($a['Start']) < new DateTime($b['Start'])) ? -1 : 1;
}



//echo dirname(__FILE__)."\n";

$topdirectory=dirname(__FILE__)."/../../MSWebGUI";
include $topdirectory.'/FileList.php';

$topdirectory=dirname(__FILE__)."/..";
include $topdirectory.'/FileList.php';


$xml = simplexml_load_file(dirname(__FILE__).'/../config/config.xml');
$databaseXML = $xml->Database;
$database = new Database($databaseXML->Server,$databaseXML->Database,$databaseXML->Username,$databaseXML->Password);

$dtNow = new DateTime("now");
$dtNow->modify('-1 week');
$valueNow = $dtNow->format("Y-m-d H:i");
$sql = "SELECT * FROM Events WHERE CreatedWhen > '".$valueNow."' OR DeletedWhen > '".$valueNow."' ORDER BY OriginalRecordID,EventID";
//echo $sql."\n";
$database->ExecuteSQL($sql);
$messageHTML = "";
$messageHTML .= "<h1>Events created, deleted or modified since ".$dtNow->format("F j, Y")."<h1>";
$messageHTML .= "<p>Deleted events are shown in <font color=\"red\">red</font>. <br>Modified events are shown in <font color=\"Orange\">orange</font>. <br>Created events are shown in <font color=\"green\">green</font>.</p>";

// get Locations
$locations = "";
$sql = "SELECT * FROM LocationNames";
$database1 = $database->Recopy();
$database1->ExecuteSQL($sql);
//	echo $sql."<br>\n";
$messageHTML .= "<table class=\"locationsList\">";
//$messageHTML .= "<p style=\"font-weight: 100;\">";
while($row1 = $database1->getRow())
{
	$messageHTML .= "<tr class=\"locationsList\"><td width=\"30px\">&nbsp</td><td class=\"locationsList\">".$row1["ShortName"]."</td><td class=\"locationsList\">".$row1["LongName"]."</td></tr>\n";
}
$messageHTML .= "</table><br><br>";	


$messageHTML .= "<table>\n";

$previousDate = new DateTime("-10 years");

$users= new Enumeration($database,'SnapUsers');
$array = $users->getArray();
//print_r($array);


$rowArray = array();

while($row = $database->getRow())
{
	array_push($rowArray,$row);
}

	// Show as deleted if Deleted and no subsequent events have originalID (!=0) set to either the first originalID or to the first eventID)
	// Show as created if Created and originalID = 0
	// Show as modified if Created and originalID != 0 and no later eventIDs with same originalID
	// Then print by Start
	
//print_r ($rowArray)."\n";
/*
foreach($rowArray as $row)
{
	echo $row['EventID']." ".$row['OriginalRecordID']." ".$row['Start']." ".$row['DeletedBy']."<br>\n";
}

echo "<br>\n------------------------<br>\n";
*/

$resultArray = array();

reset($rowArray);
//$aRow=current($rowArray);
$i=0;
while($aRow=current($rowArray))
{

//	echo "<br>\n";
//	echo "EventID: ".$aRow['EventID']." ".$aRow['DeletedBy']."<br>\n";
	
	if ($aRow['DeletedBy']!=0)
	{
		$eventID = $aRow['EventID'];
		$originalID = $aRow['OriginalRecordID'];
		$n=0;
		while(1)
		{
			if(($aRow=next($rowArray))===false)
			{
				// go back to it
				reset($rowArray);
				$aRow=current($rowArray);
				for($j=0;$j<$i;$j++)$aRow=next($rowArray);
				
				$aRow['Type']=($aRow['DeletedBy']!=0)?"Deleted":"Modified";
				array_push($resultArray,$aRow);
//				print_r($aRow)."<br>\n";
//				echo "No match on ".$aRow['EventID']." => ".$aRow['Type']." - Break<br>\n";
				// go back to next one
				break;
			}

//			echo $aRow['EventID']." ".$eventID." ".$originalID." ".$aRow['OriginalRecordID']."<br>\n";
			if(
				(($originalID == $aRow['OriginalRecordID'])&& ($originalID != 0))
			||
				($eventID == $aRow['OriginalRecordID'])
			)
			{
				//discount this one so go back to the next element
				break;
			}
			$n++;
		}
		reset($rowArray);
		for($j=0;$j<$i;$j++)$aRow=next($rowArray);
	}
	else // created
	{
		if($aRow['OriginalRecordID']==0)
		{
			$aRow['Type']="Created";
			array_push($resultArray,$aRow);
		}
		else
		{
			$originalID = $aRow['OriginalRecordID'];
			while(1)
			{
				if(($aRow=next($rowArray))===false)
				{
					
					break;
				}
			
				if($originalID != $aRow['OriginalRecordID'])
				{
					break;
				}
				$i++;
			}
			reset($rowArray);
			for($j=0;$j<$i;$j++)$aRow=next($rowArray);
			$aRow['Type']="Modified";
			array_push($resultArray,$aRow);

		}
	}
	if(($aRow=next($rowArray))===FALSE)
	{
//		echo "No more to do<br>\n";
		break;
	}
	$i++;
}

//echo "<br>\n";
/*
foreach($resultArray as $row)
{
	echo $row['EventID']." ".$row['OriginalRecordID']." ".$row['Start']." ".$row['Type']."<br>\n";
}
*/

usort($resultArray,"compare1");

$style = "";
$text = "";
$strDate = "";

foreach($resultArray as $resultRow)
{

//	echo $resultRow['DeletedBy']."\n";
	if($resultRow['Type']=="Deleted")
	{	
		$date=new datetime($resultRow['DeletedWhen']);
		$strDate = $date->format("d/m/y");
		$actor = str_replace(' ','&nbsp;',$array[$resultRow['DeletedBy']]);
		$text = "Deleted&nbsp;by:&nbsp;".$actor;
		$style = " style=\"color:red;\" ";
	}
	elseif($resultRow['Type']=="Created")
	{	
		$date=new datetime($resultRow['CreatedWhen']);
		$strDate = $date->format("d/m/y");
		$actor = str_replace(' ','&nbsp;',$array[$resultRow['CreatedBy']]);
		$text = "Created&nbsp;by:&nbsp;".$actor;
		$style = " style=\"color:green;\" ";
	}
	elseif($resultRow['Type']=="Modified")
	{	
		$date=new datetime($resultRow['CreatedWhen']);
		$strDate = $date->format("d/m/y");
		$actor = str_replace(' ','&nbsp;',$array[$resultRow['CreatedBy']]);
		$text = "Modified&nbsp;by:&nbsp;".$actor;
		$style = " style=\"color:orange;\" ";
	}
	
	
	$start = new datetime($resultRow['Start']);
	if($start->format("Y")!=$previousDate->format("Y"))$messageHTML .= printYear($start);
	if($start->format("Ym")!=$previousDate->format("Ym"))$messageHTML .= printMonth($start);
	if($start->format("Ymd")!=$previousDate->format("Ymd"))$messageHTML .= printDay($start);
	$previousDate = $start;
	
	// get Locations
	
	$locations = "";
	$sql = "SELECT id.LocationNameID, n.ShortName FROM Locations as id INNER JOIN LocationNames as n ON id.LocationNameID = n.LocationNameID WHERE id.EventID = ".$resultRow['EventID'];
	$database1 = $database->Recopy();
	$database1->ExecuteSQL($sql);
//	echo $sql."<br>\n";
	while($row1 = $database1->getRow())
	{
		$locations .= $row1["ShortName"]." ";
	}
	
	
	$startString = $start->format("H:i");
	$messageHTML .= "<tr><td width=\"20px\"></td><td".$style.">".$startString."</td><td".$style.">".$resultRow['Title']."</td><td".$style.">".$locations."</td><td".$style.">".$resultRow['Description']."</td><td".$style.">".$text."&nbsp;".$strDate."</td></tr>\n";
}
$messageHTML .= "</table>\n";
//echo $messageHTML."<br>";

$message = new Message();

$sql = "SELECT Email FROM SnapUsers";
$database->ExecuteSQL($sql);

while($row = $database->getRow())
{
	if($row['Email']!="")$message->addRecipient($row['Email']);
}

//$message->addRecipient("michael@michaelstoddart.org");
//$message->addRecipient("michael.stoddart@stnicholascathedral.co.uk");
$message->setSubject("SNAP updates since ".$dtNow->format("F j, Y"));
$message->setFrom("St Nicholas Activity Planning<no-reply@stnicholascathedralmusic.co.uk>");
$message->addStyle("p, td {font-family: sans-serif;vertical-align:top;font-size: 70%;color:#003366;}");
$message->addStyle("h1{font-family: sans-serif;font-size: 100%;color:#003366;}");
$message->addStyle("h2{font-family: sans-serif;font-size: 80%;color:#003366;}");
$message->addStyle("td.EventReportYear{font-family: sans-serif;font-size: 120%;font-weight: 400; color:#003366;}");
$message->addStyle("td.EventReportMonth{font-family: sans-serif;font-size: 110%;font-weight: 400; color:#003366;}");
$message->addStyle("td.EventReportDay{font-family: sans-serif;font-size: 80%;font-weight: 400; color:#003366;}");
$message->addStyle("table.locationsList {color: #332233; font-weight: 100; padding: 0px; margin: 0px;}");
$message->addStyle("tr.locationsList {color: #332233; font-weight: 100; padding: 0px; margin: 0px;}");
$message->addStyle("td.locationsList {color: #332233; font-weight: 100; padding: 0px; margin: 0px;}");
$message->setMessageBody($messageHTML);
$message->send();
?>